﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using ERP_RORZE_SYSTEM;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.Business
{
    public partial class frm_Add_PODetails : Form
    {
        private INISettings mySetting;
        private SqlConnection myConnection;

        private string supplier;
        private string payment;
        private string unit;
        
        public frm_Add_PODetails()
        {
            InitializeComponent();
            this.mySetting = new INISettings();
            this.myConnection = new SqlConnection(mySetting.SQLConnectionString);
            this.supplier = null;
            this.payment = null;
            getDepartmentList();
            getPOIDList();
            this.poID.Properties.AutoComplete = true;
            getPartNoList();
            this.partNo.Properties.AutoComplete = true;
            getTrasportIDList();
            this.transportationID.Properties.AutoComplete = true;
            getDeliveryList();
            this.deliveryID.Properties.AutoComplete = true;

            this.quantityValue.LostFocus += quantityValue_LostFocus;
        }

        //////////////////////////////////////////////
        /// Create Department List
        /////////////////////////////////////////////
        private void getDepartmentList()
        {
            ComboboxItem[] item = new ComboboxItem[5];
            item[0] = new ComboboxItem();
            item[0].Text = "Production (PT)";
            item[0].Value = "PT";

            item[1] = new ComboboxItem();
            item[1].Text = "Assembly (AT)";
            item[1].Value = "AT";

            item[2] = new ComboboxItem();
            item[2].Text = "Administration (GA)";
            item[2].Value = "GA";

            item[3] = new ComboboxItem();
            item[3].Text = "Business (BT)";
            item[3].Value = "BT";

            item[4] = new ComboboxItem();
            item[4].Text = "Accounting (KT)";
            item[4].Value = "KT";

            for (int i = 0; i < 5; i++)
            {
                this.departmentID.Properties.Items.Add(item[i]);
            }
        }

        //////////////////////////////////////////
        /// Calculate Price when change quantity 
        //////////////////////////////////////////
        void quantityValue_LostFocus(object sender, EventArgs e)
        {
            float quantity = 0;
            if (!string.IsNullOrEmpty(this.quantityValue.Text))
            {
                if (float.TryParse(this.quantityValue.Text, out quantity))
                {
                    if (quantity > 0)
                    {
                        if (this.partNo.SelectedIndex >= 0)
                        {
                            ComboboxItem partno = (ComboboxItem)this.partNo.SelectedItem;
                            calculatePrice(partno.Value.ToString(), quantity);
                        }
                    }
                    else
                    {
                        MessageBox.Show("The quantity must be greater than 0.");
                    }
                }
                else
                {
                    MessageBox.Show("Invalid quantity");
                }
            }
        }

        ///////////////////////////////////
        /// Check MOQ and Marume
        //////////////////////////////////
        private string checkQuantity(string part, float qty)
        {
            string ret = null;
            float moq = 0;
            float marume = 0;
            try
            {
                myConnection.Open();
                SqlCommand sql_cmd = myConnection.CreateCommand();
                sql_cmd.CommandText = "SELECT [Marume], [MinQuantity]"
                    + " FROM [222_Part] WHERE PartNo='" + part + "'";
                SqlDataReader dataReader = sql_cmd.ExecuteReader();
                if (dataReader.Read())
                {
                    if (dataReader["MinQuantity"] != DBNull.Value)
                    {
                        if (float.TryParse(dataReader["MinQuantity"].ToString(), out moq))
                        {
                            if (moq == 0) moq = 1;
                        }
                        else
                        {
                            moq = 1;
                        }
                    }
                    else
                    {
                        moq = 1;
                    }

                    if (dataReader["Marume"] != DBNull.Value)
                    {
                        if (float.TryParse(dataReader["Marume"].ToString(), out marume))
                        {
                            if (marume == 0) marume = 1;
                        }
                        else
                        {
                            marume = 1;
                        }
                    }
                    else
                    {
                        marume = 1;
                    }
                }
                else
                {
                    ret = "Invalid PartNo: " + part;
                }
                dataReader.Close();
                sql_cmd.Dispose();
                myConnection.Close();

                if (qty < moq)
                {
                    ret = "Quantity must be greater than MOQ: " + moq.ToString() + "\r\n";                    
                }
                else
                {
                    if ((qty % marume) > 0)
                    {
                        ret = "Quantity must be divisible by Marume: " + marume.ToString();
                    }
                }
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                ret = "Cannot check MOQ and Marume.";
            }
            return ret;
        }
        

        ////////////////////////////////////////////////
        /// Get POID List to form
        //////////////////////////////////////////////// 
        private void getPOIDList()
        {
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT ID FROM [241_PO] WHERE Finished=0 ORDER BY ID";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                while (dataReader.Read())
                {
                    ComboboxItem item = new ComboboxItem();
                    item.Text = dataReader["ID"].ToString();
                    item.Value = dataReader["ID"].ToString();
                    this.poID.Properties.Items.Add(item);
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("Cannot get POID list");
            }
        }        

        ////////////////////////////////////////////////
        /// Get PartNo List to form
        //////////////////////////////////////////////// 
        private void getPartNoList()
        {   
            /////////////////////////////////////////////////////////////
            /// Get PartNo compactable with Supplier ID in Quotation
            ////////////////////////////////////////////////////////////
            //MessageBox.Show(supplier);
            if (!string.IsNullOrEmpty(supplier))
            {
                try
                {
                    myConnection.Open();
                    SqlCommand cmd_sql = myConnection.CreateCommand();
                    cmd_sql.CommandText = "SELECT DISTINCT t1.[PartNo], t2.BuyingUnit"
                        + " FROM [241_ImportQuotation] AS t1 LEFT OUTER JOIN"
                        + " [222_Part] AS t2 ON t1.[PartNo]=t2.[PartNo]"
                        + " WHERE t1.[SupplierID]='" + supplier + "'";
                    SqlDataReader dataReader = cmd_sql.ExecuteReader();
                    while (dataReader.Read())
                    {
                        ComboboxItem item = new ComboboxItem();
                        item.Text = dataReader["PartNo"].ToString();
                        item.Value = dataReader["PartNo"].ToString();
                        this.partNo.Properties.Items.Add(item);

                        this.unit = dataReader["BuyingUnit"].ToString().Trim();
                    }
                    dataReader.Close();
                    cmd_sql.Dispose();
                    myConnection.Close();
                }
                catch (Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    MessageBox.Show(ex.Message);
                    //MessageBox.Show("Cannot get PartNo list");
                }
            }
        }

        ////////////////////////////////////////////////
        /// Get TransportID List to form
        //////////////////////////////////////////////// 
        private void getTrasportIDList()
        {
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT TransportID, TransportName FROM [222_Transport] ORDER BY TransportName";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                while (dataReader.Read())
                {
                    ComboboxItem item = new ComboboxItem();
                    item.Text = dataReader["TransportName"].ToString();
                    item.Value = dataReader["TransportID"].ToString();
                    this.transportationID.Properties.Items.Add(item);
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("Cannot get Trasportation list");
            }
        }

        ////////////////////////////////////////////////
        /// Load Delivery list to form
        ////////////////////////////////////////////////
        private void getDeliveryList()
        {
            try
            {
                myConnection.Open();
                SqlCommand cmd_sql = myConnection.CreateCommand();
                cmd_sql.CommandText = "SELECT DeliveryTermID, DeliveryTermName FROM [222_DeliveryTerm] ORDER BY DeliveryTermName";
                SqlDataReader dataReader = cmd_sql.ExecuteReader();
                while (dataReader.Read())
                {
                    ComboboxItem item = new ComboboxItem();
                    item.Text = dataReader["DeliveryTermName"].ToString();
                    item.Value = dataReader["DeliveryTermID"].ToString();
                    this.deliveryID.Properties.Items.Add(item);
                }
                dataReader.Close();
                cmd_sql.Dispose();
                myConnection.Close();
            }
            catch
            {
                if (myConnection.State == ConnectionState.Open)
                {
                    myConnection.Close();
                }
                MessageBox.Show("Cannot get Delivery list");
            }
        }

        private void buttonCancel_Click(object sender, EventArgs e)
        {
            this.Dispose();
        }

        private void buttonAdd_Click(object sender, EventArgs e)
        {
            string error = null;

            string poid = null;
            string orderno = null;
            string partno = null;
            int quantity = 0;
            float price = 0;
            DateTime deliverydate = new DateTime();
            string transport = null;
            string delivery = null;
            string purpose = null;
            string note = null;
            string currency = null;

            ComboboxItem item = new ComboboxItem();

            if (this.poID.SelectedIndex < 0)
            {
                error = "Please select POID";
            }
            else
            {
                item = (ComboboxItem)this.poID.SelectedItem;
                poid = item.Value.ToString();
            }

            if (string.IsNullOrEmpty(error))
            {
                if (string.IsNullOrEmpty(this.orderNo.Text))
                {
                    error = "Please enter Order No.";
                }
                else
                {
                    orderno = this.orderNo.Text;
                }
            }

            if (string.IsNullOrEmpty(error))
            {
                if (this.partNo.SelectedIndex < 0)
                {
                    error = "Please select Part No.";
                }
                else
                {
                    item = (ComboboxItem)this.partNo.SelectedItem;
                    partno = item.Value.ToString();
                }
            }

            if (string.IsNullOrEmpty(error))
            {
                if (string.IsNullOrEmpty(this.quantityValue.Text))
                {
                    error = "Please enter Quantity";
                }
                else
                {
                    if (!Int32.TryParse(this.quantityValue.Text, out quantity))
                    {
                        error = "Invalid quantity";
                    }
                    else
                    {
                        if (quantity <= 0)
                        {
                            error = "The quantity must be greater than 0.";
                        }
                    }
                }
            }

            if (string.IsNullOrEmpty(error))
            {
                if (this.deliveryDate.EditValue == null)
                {
                    error = "Please select Delivery Date";
                }
                else
                {
                    DateTime.TryParse(deliveryDate.EditValue.ToString(), out deliverydate);
                }
            }

            if (string.IsNullOrEmpty(error))
            {
                if (this.transportationID.SelectedIndex >= 0)
                {
                    item = (ComboboxItem)this.transportationID.SelectedItem;
                    transport = item.Value.ToString();
                }

                if (this.deliveryID.SelectedIndex >= 0)
                {
                    item = (ComboboxItem)this.deliveryID.SelectedItem;
                    delivery = item.Value.ToString();
                }

                purpose = this.purposeValue.Text;
                note = this.noteValue.Text;

                price = float.Parse(this.priceValue.Text);
                currency = this.currencySymbol.Text;

                try
                {
                    myConnection.Open();
                    SqlCommand cmd_sql = myConnection.CreateCommand();
                    ////////////////////////////////////////////////////////////
                    /// Insert to PODetails
                    ////////////////////////////////////////////////////////////               
                    string sql = "INSERT INTO [241_PODetails]"
                        + " (POID, OrderNo, PartNo, Quantity, UnitID, UnitPrice, CurrencyID, DeliveryDate, TransportID,"
                        + " DeliveryTermID, Note, Purpose) VALUES ('" + poid + "', '" + orderno + "', N'" + partno + "',"
                        + " " + quantity + ", '" + this.unit + "', " + price + ", '" + currency + "', @deliverydate,"
                        + " '" + transport + "', '" + delivery + "', '" + note + "', '" + purpose + "')";
                    cmd_sql = new SqlCommand(sql, myConnection);
                    cmd_sql.Parameters.Add("@deliverydate", SqlDbType.DateTime).Value = deliverydate.Date;
                    cmd_sql.ExecuteNonQuery();
                    cmd_sql.Dispose();
                    myConnection.Close();
                    MessageBox.Show("PODetails added success.");
                    clearForm();
                }
                catch //(Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    //MessageBox.Show(ex.Message);
                    MessageBox.Show("Cannot add PODetails. Please try again.");
                }
            }
            else
            {
                MessageBox.Show(error);
            }
        }

        private void poID_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (this.poID.SelectedIndex >= 0)
            {
                ComboboxItem poid = (ComboboxItem)poID.SelectedItem;
                try
                {
                    myConnection.Open();
                    SqlCommand cmd_sql = myConnection.CreateCommand();
                    cmd_sql.CommandText = "SELECT SupplierID, PaymentID, DeliveryTermID"
                        + " FROM [241_PO] WHERE ID='" + poid.Value.ToString() + "'";
                    SqlDataReader dataReader = cmd_sql.ExecuteReader();
                    if (dataReader.Read())
                    {
                        supplier = dataReader["SupplierID"].ToString();
                        payment = dataReader["PaymentID"].ToString();
                        for (int i = 0; i < this.deliveryID.Properties.Items.Count; i++)
                        {
                            ComboboxItem item = (ComboboxItem)this.deliveryID.Properties.Items[i];
                            if (item.Value.ToString() == dataReader["DeliveryTermID"].ToString())
                            {
                                this.deliveryID.SelectedIndex = i;
                            }
                        }
                    }
                    dataReader.Close();
                    cmd_sql.Dispose();
                    myConnection.Close();
                }
                catch (Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    MessageBox.Show(ex.Message);
                    supplier = null;
                    payment = null;
                }
                this.partNo.Properties.Items.Clear();
                getPartNoList();
            }
        }

        private void clearForm()
        {
            foreach (Control c in this.Controls)
            {
                if (c is DevExpress.XtraEditors.TextEdit)
                {
                    ((DevExpress.XtraEditors.TextEdit)c).Text = null;
                }

                if (c is DevExpress.XtraEditors.ComboBoxEdit)
                {
                    ((DevExpress.XtraEditors.ComboBoxEdit)c).SelectedIndex = -1;
                }

                if (c is System.Windows.Forms.MaskedTextBox)
                {
                    ((System.Windows.Forms.MaskedTextBox)c).Text = null;
                }
            }
        }
                
        //////////////////////////////////////
        /// Calculate price when change part
        //////////////////////////////////////
        private void partNo_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (this.partNo.SelectedIndex >= 0)
            {
                ComboboxItem partno = new ComboboxItem();
                partno = (ComboboxItem)this.partNo.SelectedItem;

                float moq = 0;
                float marume = 0;
                try
                {
                    myConnection.Open();
                    SqlCommand sql_cmd = myConnection.CreateCommand();
                    sql_cmd.CommandText = "SELECT [Marume], [MinQuantity]"
                        + " FROM [222_Part] WHERE PartNo='" + partno.Value.ToString() + "'";
                    SqlDataReader dataReader = sql_cmd.ExecuteReader();
                    if (dataReader.Read())
                    {
                        if (dataReader["MinQuantity"] != DBNull.Value)
                        {
                            if (float.TryParse(dataReader["MinQuantity"].ToString(), out moq))
                            {
                                if (moq == 0) moq = 1;
                            }
                            else
                            {
                                moq = 1;
                            }
                        }
                        else
                        {
                            moq = 1;
                        }

                        if (dataReader["Marume"] != DBNull.Value)
                        {
                            if (float.TryParse(dataReader["Marume"].ToString(), out marume))
                            {
                                if (marume == 0) marume = 1;
                            }
                            else
                            {
                                marume = 1;
                            }
                        }
                        else
                        {
                            marume = 1;
                        }
                    }
                    dataReader.Close();
                    sql_cmd.Dispose();
                    myConnection.Close();

                    this.moqText.Text = moq.ToString();
                    this.marumeText.Text = marume.ToString();
                }
                catch
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }                    
                }

                if (!string.IsNullOrEmpty(this.quantityValue.Text))
                {                    
                    float quantity = 0;
                    if (float.TryParse(this.quantityValue.Text, out quantity))
                    {
                        if (quantity > 0)
                        {
                            calculatePrice(partno.Value.ToString(), quantity);
                        }
                        else
                        {
                            MessageBox.Show("Quantity must be greater than 0.");
                        }
                    }
                    else
                    {
                        MessageBox.Show("Invalid quantity");
                    }
                }
            }
        }

        //////////////////////////////////////////////
        /// Calcaulate Price with PartNo and Quantity
        //////////////////////////////////////////////
        private void calculatePrice(string part, float qty)
        {
            if (payment == "NoComercia")
            {
                this.priceValue.Text = "0";
                this.currencySymbol.Text = "USD";
            }
            else
            {
                float max_qty = 0;
                bool pass = true;
                try
                {
                    myConnection.Open();
                    SqlCommand cmd_sql = myConnection.CreateCommand();
                    cmd_sql.CommandText = "SELECT Quantity"
                        + " FROM [241_ImportQuotation] WHERE PartNo='" + part + "' AND SupplierID='" + supplier + "'"
                        + " AND Quantity < 99999"
                        + " ORDER BY Quantity DESC";
                    SqlDataReader dataReader = cmd_sql.ExecuteReader();
                    if (dataReader.Read())
                    {
                        max_qty = float.Parse(dataReader["Quantity"].ToString());                        
                    }
                    dataReader.Close();
                    cmd_sql.Dispose();
                    myConnection.Close();
                }
                catch
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    pass = false;
                }

                if (pass)
                {
                    if (qty < max_qty)
                    {
                        try
                        {
                            myConnection.Open();
                            SqlCommand cmd_sql = myConnection.CreateCommand();
                            cmd_sql.CommandText = "SELECT Price, CurrencyID"
                                + " FROM [241_ImportQuotation] WHERE PartNo='" + part + "'"
                                + " AND SupplierID='" + supplier + "' AND Quantity<=" + qty
                                + " ORDER BY Price";
                            SqlDataReader dataReader = cmd_sql.ExecuteReader();
                            if (dataReader.Read())
                            {
                                this.currencySymbol.Text = dataReader["CurrencyID"].ToString();                                
                                this.priceValue.Text = dataReader["Price"].ToString();
                            }
                            dataReader.Close();
                            cmd_sql.Dispose();
                            myConnection.Close();
                        }
                        catch
                        {
                            if (myConnection.State == ConnectionState.Open)
                            {
                                myConnection.Close();
                            }
                            MessageBox.Show("Cannot get price for this PartNo.");
                        }
                    }
                    else
                    {
                        try
                        {
                            myConnection.Open();
                            SqlCommand cmd_sql = myConnection.CreateCommand();
                            cmd_sql.CommandText = "SELECT Price, CurrencyID"
                                + " FROM [241_ImportQuotation] WHERE PartNo='" + part + "'"
                                + " AND SupplierID='" + supplier + "'"
                                + " ORDER BY Price";
                            SqlDataReader dataReader = cmd_sql.ExecuteReader();
                            if (dataReader.Read())
                            {
                                this.currencySymbol.Text = dataReader["CurrencyID"].ToString();
                                this.priceValue.Text = dataReader["Price"].ToString();
                            }
                            dataReader.Close();
                            cmd_sql.Dispose();
                            myConnection.Close();
                        }
                        catch
                        {
                            if (myConnection.State == ConnectionState.Open)
                            {
                                myConnection.Close();
                            }
                            MessageBox.Show("Cannot get price for this PartNo.");
                        }
                    }
                }
                else
                {
                    MessageBox.Show("Cannot get price for this PartNo.");
                }
            }
        }

        private void departmentID_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (this.departmentID.SelectedIndex >= 0)
            {
                string prefix = null;
                int index = 0;
                ComboboxItem item = (ComboboxItem)this.departmentID.SelectedItem;
                prefix = item.Value.ToString();
                try
                {
                    myConnection.Open();
                    SqlCommand sql_cmd = myConnection.CreateCommand();
                    sql_cmd.CommandText = "SELECT MAX(SUBSTRING(OrderNo, 3, LEN(OrderNo))) FROM [241_PODetails]"
                        + " WHERE SUBSTRING(OrderNo, 1, 2) = '" + prefix + "'";
                    SqlDataReader dataReader = sql_cmd.ExecuteReader();
                    if (dataReader.Read())
                    {
                        if (dataReader[0] != DBNull.Value)
                        {
                            Int32.TryParse(dataReader[0].ToString(), out index);
                        }
                    }
                    dataReader.Close();
                    sql_cmd.Dispose();
                    myConnection.Close();

                    index++;

                    this.orderNo.Text = prefix + index.ToString("D5");
                }
                catch //(Exception ex)
                {
                    if (myConnection.State == ConnectionState.Open)
                    {
                        myConnection.Close();
                    }
                    MessageBox.Show("Cannot create OrderNo");
                    //MessageBox.Show(ex.Message);
                }
            }
        }
    }
}
